IF OBJECT_ID('dbo.RptPopulateAgreementInfoTable') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptPopulateAgreementInfoTable
    IF OBJECT_ID('dbo.RptPopulateAgreementInfoTable') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptPopulateAgreementInfoTable >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptPopulateAgreementInfoTable >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.RptPopulateAgreementInfoTable
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 8/29/2007
--
-- Description: Populates two cached tables..
--             dbo.AgreementBudgets
--             dbo.Agreement_Info
--
-- Parameters:  
--              
--              
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.RptPopulateAgreementInfoTable( )
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 8/29/07     KC        Created
-- 9/7/07      KC        Added update for Award stuff
-- 9/14/07     KC        Added PreAwardManagerStuff
-- 9/21/07     KC        Added InstitutionId, DeptId, UnitId, 
--                            ProtocolApprovalDate
-- 9/27/07     KC        Fixed bug for PreAwardManager,
--                       Added dbo.RexStatusHistory.ReceivedDate
-- 9/28/07     KC        Added PROJFUNDNUMBER
-- 10/5/07     KC        Added SubContractor Info.
-- 10/8/07     KC        Added Update for ProjectFundNumber.
-- 10/15/07    KC        Commented out SubContractor Update. There is a 
--                       potential Many-to-One relationship here.
-- 11/12/07    KC        Added IsCurrent WHERE clauses due to loader 
--                       changes...
-- 01/25/08    KC        Added PSLocCode & SponsorGroupTypeId to Table, and here.
-- 02/21/08    KC        Added StatusId=4 (Active) to map list for Awarded
--
-- =======================================================================
CREATE PROCEDURE dbo.RptPopulateAgreementInfoTable
AS
BEGIN
     -- Initial Settings
     SET NOCOUNT ON
     SET ANSI_WARNINGS OFF
     
     -- Declare Local Variables
     DECLARE   @Error            INT,
               @RowCount         INT

     -- First, Clear target table for Budgets
     TRUNCATE TABLE dbo.AgrAgreement_Info

     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
          RETURN -1

     -- Put rows in using View
     INSERT dbo.AgrAgreement_Info
         (AgreementId,
          FolderNumber,
          DocumentNumber,
          ResourceId,
          SvcEntityId,
          ParentFolderNumber,
          InfoEdPropNumber,
          ProjectFundNumber,
          PrincipalInvestigatorId,
          PrincipalInvestigator,
          ProposalTitle,
          ProposalShortTitle,
          Institution,
          InstitutionId,
          Department,
          DepartmentId,
          Unit,
          UnitId,
          ChiefCode,
          PSLocCode,
          SponsorID,
          SponsorTypeId,
          SponsorTypeName,
          SponsorName,
          SponsorNumber,
          SponsorAgreementID,
          InfoEdSponsorNumber,
          SecondarySponsor,
          SponsorGroupTypeId,
          SubcontractsYN,
          SubContractorId,
          SubContractorName,
          SubContractorPIName,
          ProjectStartDate,
          ProjectEndDate,
          ReceivedDate,
          DeadlineDate,
          FullyExecutedAwardedDate,
          ProtocolApprovalDate,
          AgreementStatusId,
          ProposalStatus,
          OverallStatus,
          MasterProposalStatus,
          BudgetPeriodStatus,
          TransactionTypeId,
          TransactionType,
          AgreementTypeId,
          AgreementType,
          ActivityCode,
          ActivityDescription,
          InstrumentType,
          SubActivityType,
          IndirectCostRate,
          ICBasis,
          PostAwardManagerCode,
          PostAwardManagerName,
          PostAwardManagerId,
          CSCRManagerId,
          CSCRCaseManager,
          CSCRCaseManagerName,
          PreAwardManagerId,
          PreAwardManager,
          PreAwardManagerName,
          ApprovalNumber,
          BackupAccountNumber,
          CostShareType,
          CancerRelatedYN,
          BiohazardYN,
          EmbryonicStemCells,
          StudyHumanYN,
          StudyAnimalYN,
          AdvanceFundYN,
          RecombinantDNAYN,
		  PIUniqueId)
     SELECT DISTINCT
          A.Id,                         --     AgreementId,
          A.FolderNumber,               --     FolderNumber,
          A.DocumentNumber,             --     DocumentNumber,
          A.FwkResourceId,              --     ResourceId,
          A.SvcEntityId,                --     SvcEntityId,
          AH.ParentFolderNumber,        --     ParentFolderNumber,
          A.PropNumber,                 --     InfoEdPropNumber,
          NULL,                         --     ProjectFundNumber,
          A.PIPersonId,                 --     PrincipalInvestigatorId,
          RTRIM(P.LastName) + ', ' + RTRIM(P.FirstName) + RTRIM(IsNull(' ' + P.MiddleName,''))
                    + IsNull('  ' + dbo.fnGetDegree(A.PIPersonId),''),--     PrincipalInvestigator,
          SUBSTRING(A.Title,1,512),     --     ProposalTitle,
          A.ShortTitle,                 --     ProposalShortTitle,
          AF.Institution,               --     Institution,
          ORGS.InstitutionId,           --     InstitutionId,
          AF.Department,                --     Department,
          ORGS.DepartmentId,            --     DepartmentId
          AF.Unit,                      --     Unit,
          ORGS.UnitId,                  --     UnitId
          AF.ChiefCode,                 --     ChiefCode,
          AF.FACPSCode,                 --     PSLocCode
          SPM.AdmSponsorId,             --     SponsorID,
          SP.AdmSponsorTypeId,          --     SponsorTypeId,
          SPT.Name,                     --     SponsorTypeName,
          SP.Name,                      --     SponsorName,
          SP.InfoEdSponCode,            --     SponsorNumber,
          SPM.SponsorNumber,            --     SponsorAgreementID,
          SP.InfoEdSponCode,            --     InfoEdSponsorNumber,
          SSP.Name,                     --     SecondarySponsor,
          ATGTM.AdmSponsorGroupTypeId,  --     SponsorGroupTypeId,
          NULL,                         --     SubcontractsYN,
          NULL,                         --     SubContractorId,
          NULL,                         --     SubContractorName,
          NULL,                         --     SubContractorPIName,
          A.ProjectStartDate,           --     ProjectStartDate,
          A.ProjectEndDate,             --     ProjectEndDate,
          ISNULL(RSH.ReceivedDate,ISNULL(PROP.create_dt,PROP.submt_dt)),   --     ReceivedDate,
          PROP.deadline,                --     DeadlineDate,
          UDF.FullyExecutedAwardedDate, --     FullyExecutedAwardedDate,
          NULL,                         --     ProtocolApprovalDate
          A.AgrAgreementStatusTypeId,   --     AgreementStatusId,
          ST.Name,                      --     ProposalStatus,
          A.OverallStatus,              --     OverallStatus,
          -- If ProjectEndDate has passed, but Status is not closed, tag as Ended
          CASE WHEN A.ProjectEndDate < GETDATE() AND A.AgrAgreementStatusTypeId NOT IN (6,7,12,16,17,18,19,20) THEN 'Ended'
               WHEN A.AgrAgreementStatusTypeId IN (2,3,4,21,23,24,25) THEN 'Awarded'
               WHEN A.AgrAgreementStatusTypeId IN (1,10,11,15,22,26,27,28,29,30,31,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55) THEN 'Pending'
               WHEN A.AgrAgreementStatusTypeId IN (6,7,12,16,17,18,19,20) THEN 'Closed'
               ELSE ST.Name 
          END,                          --     MasterProposalStatus,
          NULL,                         --     BudgetPeriodStatus
          A.AgrTransactionTypeId,       --     TransactionTypeId,
          TT.Name,                      --     TransactionType,
          A.AgrAgreementTypeId,         --     AgreementTypeId,
          AT.Name,                      --     AgreementType,
          AF.PSActivityCode,            --     ActivityCode,
          AF.PSActivityDescription,     --     ActivityDescription,
          UDF.InstrumentType,           --     InstrumentType,
          UDF.SubActivityType,          --     SubActivityType,
          NULL,                         --     IndirectCostRate,
          NULL,                         --     ICBasis,
          UDF.PostAwardManager,         --     PostAwardManagerCode,
          PAM.ManagerName,              --     PostAwardManagerName,
          PAM.PersonId,                 --     PostAwardManagerId,
          CSM.PersonId,                 --     CSCRManagerId,
          UDF.CSRCaseManager,           --     CSCRCaseManager,
          CSM.ManagerName,              --     CSCRCaseManagerName,
          PrAM.PersonId,                --     PreAwardManagerId 
          UDF.PreAwardManager,          --     PreAwardManager   
          PrAM.ManagerName,             --     PreAwardManagerName 
          NULL,                         --     ApprovalNumber,
          NULL,                         --     BackupAccountNumber,
          CASE WHEN PROP.cst_v = '1' THEN 'VoluntaryCommitted'
               WHEN PROP.cst_m = '1' THEN 'Mandatory'
               WHEN PROP.cst_c = '1' THEN 'Mandatory&VoluntaryCommitted'
               WHEN PROP.cst_n = '1' THEN 'None'
               ELSE NULL
          END,                          --     CostShareType,
          UDF.CancerRelatedYN,          --     CancerRelatedYN,
          CASE WHEN UDF.Biohazard=1
               THEN 'Yes'
               ELSE 'No'
          END,                          --     BiohazardYN,
          NULL,                         --     EmbryonicStemCells,
          UDF.HumansYN,                 --     StudyHumanYN,
          UDF.AnimalsYN,                --     StudyAnimalYN,
          NULL,                         --     AdvanceFundYN,
          CASE WHEN UDF.RecombinantDNA=1
               THEN 'Yes'
               ELSE 'No'
          END,                          --     RecombinantDNAYN
		  IsNull(A.PIUniqueId,apfm.Unique_Id) as 'PIUniqueId'
     FROM 
          dbo.AgrAgreement A 
               LEFT OUTER JOIN dbo.AgrAgreementHierarchy AH ON A.FolderNumber = AH.FolderNumber
               LEFT OUTER JOIN dbo.AdmPerson P ON A.PIPersonId = P.Id
			   LEFT OUTER JOIN dbo.AdmPerson_InfoEdFaculty_Map apfm on p.Id = apfm.admpersonid
											and apfm.unique_id = (select max(apfm1.Unique_id) 
																  from AdmPerson_InfoEdFaculty_Map apfm1 
																  where apfm1.admpersonid = apfm.admpersonid)
               LEFT OUTER JOIN dbo.AgrTransactionType TT ON A.AgrTransactionTypeId = TT.Id
               INNER JOIN dbo.AgrAgreementType AT ON A.AgrAgreementTypeId = AT.Id
               INNER JOIN dbo.AgrAgreementStatusType ST ON A.AgrAgreementStatusTypeId = ST.Id
               LEFT OUTER JOIN dbo.SvcEntity_AdmSponsor_Map SPM ON A.SvcEntityId = SPM.SvcEntityId AND SPM.AdmSponsorRelationTypeId = 1
               LEFT OUTER JOIN dbo.AdmSponsor SP ON SPM.AdmSponsorId = SP.Id
               LEFT OUTER JOIN dbo.AdmSponsorType SPT ON SP.AdmSponsorTypeId = SPT.Id
               LEFT OUTER JOIN dbo.AdmSponsorType_GroupType_Map ATGTM ON SPT.Id = ATGTM.AdmSponsorTypeId
               LEFT OUTER JOIN dbo.InfoEdProposal PROP ON A.PropNumber = PROP.prop_no AND PROP.inst_code = 'PARTNERS'
               LEFT OUTER JOIN dbo.InfoEdUDFProposal UDF ON A.PropNumber = UDF.PropNumber AND UDF.InstCode = 'PARTNERS'
               LEFT OUTER JOIN (SELECT MIN(Name) AS Name, InfoEdSponCode FROM dbo.AdmSponsor 
                              GROUP BY InfoEdSponCode) SSP ON PROP.orig_spon = SSP.InfoEdSponCode
               LEFT OUTER JOIN (SELECT DISTINCT AgreementId AS AgreementId,
                                   FolderNumber AS FolderNumber,
                                   Institution AS Institution,
                                   Department AS Department,
                                   ChiefCode AS ChiefCode,
                                   FAC.PSCode AS FACPSCode,
                                   Unit AS Unit,
                                   RTRIM(PILastName) + ', ' + RTRIM(PIFirstName) + ISNULL(' ' + RTRIM(PIMiddleName),'') 
                                        + IsNull('  ' + dbo.fnGetDegree(PrincipalInvestigatorId),'') AS PrincipalInvestigator,
                                   ActivityTypeId AS ActivityTypeId,
                                   PSActivityCode AS PSActivityCode,
                                   PSActivityDescription AS PSActivityDescription
                              FROM dbo.AgrAgreement_Fct 
                                   LEFT OUTER JOIN dbo.AdmFacility FAC ON FacilityId = FAC.Id) AF ON A.Id = AF.AgreementId
               LEFT OUTER JOIN dbo.vwInfoEdPostAwardManager PAM ON UDF.PostAwardManager = PAM.PostAwardManager
               LEFT OUTER JOIN dbo.vwInfoEdCSCRCaseManager CSM ON UDF.CSRCaseManager = CSM.CSRCaseManager
               LEFT OUTER JOIN dbo.vwInfoEdPreAwardManager PrAM ON UDF.PreAwardManager = PrAM.PreAwardManager
               LEFT OUTER JOIN (SELECT    MAP.SvcEntityId AS SvcEntityId,
                                        O.InstitutionId AS InstitutionId, 
                                        O.DepartmentId AS DepartmentId, 
                                        O.UnitId AS UnitId
                              FROM      dbo.SvcEntity_FwkDomainOrganization_Map MAP 
                                        INNER JOIN dbo.FwkDomainOrganization O ON MAP.FwkDomainOrganizationId = O.Id ) ORGS
                    ON A.SvcEntityId = ORGS.SvcEntityId                                           
               -- RexStatusHistory for ReceivedDate
               LEFT OUTER JOIN dbo.RexStatusHistory RSH ON A.FolderNumber = RSH.FolderNumber
              /* LEFT OUTER JOIN (SELECT    FAAM.AgrAgreementId,
                                        FAAM.FundNumber
                              FROM      dbo.AgrFund_AgrAgreement_Map FAAM 
                                        INNER JOIN (SELECT    AgrAgreementId,
                                                              MAX(AgrFundId) AS LastAgrFundId
                                                    FROM      dbo.AgrFund_AgrAgreement_Map
                                                    GROUP BY  AgrAgreementId) LAST ON FAAM.AgrAgreementId = LAST.AgrAgreementId AND
                                                            FAAM.AgrFundId = LAST.LastAgrFundId ) PRJFUND ON A.Id = PRJFUND.AgrAgreementId
          */
     WHERE     A.IsCurrent = 1



     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
          RETURN -1          
     
     -- Update Award Fields
     UPDATE    dbo.AgrAgreement_Info
     SET       AdvanceFundYN = AWARDS.AdvanceFundYN,
               EmbryonicStemCells = AWARDS.EmbryonicStemCells,
               BackupAccountNumber = AWARDS.BackupAccountNumber
     FROM      (SELECT    A.PropNumber,
                         A.SubNum,
                         LAST.SubNum AS LastSubNum,
                         CASE WHEN A.AdvanceFundYN = 0
                              THEN 'No'
                              ELSE 'Yes'
                         END AS AdvanceFundYN,
                         A.BackupAccountNumber,
                         A.EmbryonicStemCells
               FROM      dbo.InfoEdUDFAward A
                         INNER JOIN (SELECT   PropNumber,
                                              MAX(CAST(RIGHT(SubNum,15) AS NUMERIC(15,0))) AS SubNum
                                    FROM      dbo.InfoEdUDFAward 
                                    WHERE     InstCode = 'PARTNERS'
                                    GROUP BY  PropNumber) AS LAST ON A.PropNumber = LAST.PropNumber AND
                                                  CAST(RIGHT(A.SubNum,15) AS NUMERIC(15,0)) = LAST.SubNum ) AWARDS
     WHERE     dbo.AgrAgreement_Info.InfoEdPropNumber = AWARDS.PropNumber                                                  

     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
          RETURN -1   
          
     -- Update ProtocolApprovalDate
     UPDATE    dbo.AgrAgreement_Info
     SET       IRBApprovalStatus =appr_stat,
               IRBApprovalDateFrom =apr_from,
               IRBApprovalDateTo =apr_to
     FROM      dbo.InfoEdAppXref APPX
     WHERE     dbo.AgrAgreement_Info.InfoEdPropNumber = APPX.prop_no AND APPX.inst_code = 'PARTNERS'

     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
          RETURN -1   

     -- Update Subcontractor Info
     --UPDATE    dbo.AgrAgreement_Info
     --SET       SubcontractsYN = 'Y',
     --          SubContractorId = SC.Id,
     --          SubContractorName = SC.Name,
     --          SubContractorPIName = 'NOT AVAILABLE'
     --FROM      dbo.AgrAgreement_Info AI 
     --          INNER JOIN dbo.AgrAgreement_SubContractor_Map SCM ON AI.AgreementId = SCM.AgrAgreementId 
     --          INNER JOIN dbo.AgrSubContractor SC ON SCM.AgrSubContractorId = SC.Id 
     --
     --SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     --IF @Error!=0
     --     RETURN -1   

     -- Update ProjectFundNumber, this gets the lastest, by LastActivationDate and FundId
     UPDATE    dbo.AgrAgreement_Info
     SET       ProjectFundNumber = LASTFNbr.FundNumber
     FROM      dbo.AgrAgreement_Info AI 
               INNER JOIN (SELECT    FAM.AgrAgreementId,
                                      FAM.FundNumber
                            FROM      dbo.AgrFund_AgrAgreement_Map FAM INNER JOIN       
                                      (SELECT    M.AgrAgreementId,
                                                  MAX(M.AgrFundId) AS AgrFundId
                                        FROM      dbo.AgrFund_AgrAgreement_Map M INNER JOIN
                                                  (SELECT M.AgrAgreementId,
                                                          MAX(M.ActivationDate) AS ActivationDate
                                                  FROM    dbo.AgrFund_AgrAgreement_Map M
                                                  GROUP BY M.AgrAgreementId) LASTActDate ON M.AgrAgreementId = LASTActDate.AgrAgreementId 
                                                            AND M.ActivationDate = LASTActDate.ActivationDate
                                        GROUP BY M.AgrAgreementId) LastFundId ON FAM.AgrAgreementId = LastFundId.AgrAgreementId
                                                AND FAM.AgrFundId = LastFundId.AgrFundId ) LASTFNbr ON AI.AgreementId = LASTFNbr.AgrAgreementId


     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
          RETURN -1   

     -- Return Success
     RETURN 0
     
END
go

IF OBJECT_ID('dbo.RptPopulateAgreementInfoTable') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptPopulateAgreementInfoTable >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptPopulateAgreementInfoTable >>>'
go

GRANT EXECUTE ON dbo.RptPopulateAgreementInfoTable TO InsightReport,Insight
go